if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PreservationNoticeHistoryGetBySiteCollection]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].PreservationNoticeHistoryGetBySiteCollection
GO

CREATE PROCEDURE dbo.[PreservationNoticeHistoryGetBySiteCollection]
(
	@ServerInstanceName nvarchar(128),
	@ConfigDatabaseName nvarchar(128),
	@SiteCollectionID uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE
	@TwoPartsName nvarchar(256),
	@ContentDatabaseName nvarchar(128),
	@SQL nvarchar(4000)

CREATE TABLE #DatabaseNames
	(
	DatabaseName nvarchar(128)
	)

SET @TwoPartsName = '[' + @ServerInstanceName + '].[' + @ConfigDatabaseName + ']'
SET @SQL = 'SELECT 
				b.Name 
			FROM 
				' + @TwoPartsName + '.[dbo].[SiteMap] AS a INNER JOIN 
				' + @TwoPartsName + '.[dbo].[Objects] AS b ON a.DatabaseId = b.[Id]
				INNER JOIN ' + @TwoPartsName + '.[dbo].[Objects] AS c 
				ON b.ParentId=c.[Id] 
			WHERE 
				a.[Id] = ' + '''' + CAST(@SiteCollectionID AS nvarchar(40)) + ''''
	
INSERT INTO #DatabaseNames
EXECUTE(@SQL)

SELECT TOP 1 
	@ContentDatabaseName = DatabaseName 
FROM 
	#DatabaseNames
	
DROP TABLE #DatabaseNames

SET @TwoPartsName = '[' + @ServerInstanceName + '].[' + @ContentDatabaseName + ']'
SET @SQL = 'SELECT Id, Title FROM ' + @TwoPartsName + '.[dbo].[Webs] WHERE SiteId = ' + '''' + CAST(@SiteCollectionID AS nvarchar(40)) + ''''

CREATE TABLE #Webs
	(
	WebId uniqueidentifier,
	Title nvarchar(255)
	)

INSERT INTO #Webs
EXEC(@SQL)

SELECT
	PreservationNoticeHistoryID,
	NewSiteID AS SiteID,
	w.Title AS SiteName,
	PreservationNoticeID,
	EventType,
	PreservationNoticeName,
	DateEmailMessageCreated,
	DateEmailMessageSent,
	DateAcknowledged,
	FromAddress,
	MessageSubject, 
	Identifier,
	CustodianID,
	CustodianLastName,
	CustodianFirstName,
	CustodianEmail
FROM 
	PreservationNoticeHistory pnh
	INNER JOIN #Webs w
	ON pnh.NewSiteID = w.WebId
ORDER BY
	PreservationNoticeHistoryID DESC

DROP TABLE #Webs
GO